import pymysql
from pymysql.cursors import DictCursor

# 数据库连接配置
db_config = {
    'host': '???',
    'user': 'root',
    'password': '???',
    'db': 'industry',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor,
}

# 连接到数据库
connection = pymysql.connect(**db_config)

try:
    with connection.cursor() as cursor:
        # 检查并删除旧的表（如果存在）
        cursor.execute("DROP TABLE IF EXISTS patent_cooperate")

        # 创建新表patent_cooperate
        create_table_sql = '''
                CREATE TABLE IF NOT EXISTS patent_cooperate (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    company_one VARCHAR(255),
                    company_two VARCHAR(255),
                    company_one_longitude DECIMAL(10, 7),
                    company_one_latitude DECIMAL(10, 7),
                    company_two_longitude DECIMAL(10, 7),
                    company_two_latitude DECIMAL(10, 7),
                    cooperate_number INT
                );
                '''
        cursor.execute(create_table_sql)

        # 插入合作信息
        insert_cooperate_sql = '''
                INSERT INTO patent_cooperate (company_one, company_two, company_one_longitude, company_one_latitude, company_two_longitude, company_two_latitude, cooperate_number)
                SELECT 
                    LEAST(c1.company_name, c2.company_name) AS company_one,
                    GREATEST(c1.company_name, c2.company_name) AS company_two,
                    c1.longitude AS company_one_longitude,
                    c1.latitude AS company_one_latitude,
                    c2.longitude AS company_two_longitude,
                    c2.latitude AS company_two_latitude,
                    COUNT(*) AS cooperate_number
                FROM 
                    (SELECT DISTINCT applicant, publication_number FROM patent_p) p
                JOIN 
                    companies c1 ON p.applicant = c1.company_name
                JOIN 
                    (SELECT DISTINCT applicant AS applicant2, publication_number FROM patent_p) p2 ON p.publication_number = p2.publication_number AND p.applicant < p2.applicant2
                JOIN 
                    companies c2 ON p2.applicant2 = c2.company_name
                GROUP BY 
                    LEAST(c1.company_name, c2.company_name),
                    GREATEST(c1.company_name, c2.company_name),
                    c1.longitude, c1.latitude, c2.longitude, c2.latitude;
                '''
        cursor.execute(insert_cooperate_sql)

        # 提交事务
        connection.commit()
finally:
    connection.close()